﻿CREATE PROCEDURE [dbo].[utl_PruneTable]
@Table [sysname]=NULL, @HowMuch VARCHAR (50)=NULL, @ProcessName [sysname]=NULL, @Instance UNIQUEIDENTIFIER=NULL
AS
BEGIN
	DECLARE @True  AS BIT; SET @True  = 1
	DECLARE @False AS BIT; SET @False = 0
	
	-- start of utl_LogMsg setup --
	DECLARE @Message        VARCHAR(MAX);        SET @Message = NULL
	DECLARE @RowsAffected   INT;                 SET @RowsAffected = NULL
	DECLARE @ErrorCode      INT;                 SET @ErrorCode = NULL
	/*DECLARE @ProcessName    sysname;           SET @ProcessName = NULL*/
	DECLARE @StepName       sysname;             SET @StepName = OBJECT_NAME(@@PROCID)
    /*DECLARE @Instanct       UNIQUEIDENTIFIER*/ SET @Instance = ISNULL(@Instance, NEWID())
	-- end of utl_LogMsg setup --
	
	DECLARE @Days             INT
	DECLARE @Records          INT
	DECLARE @PriorToDate      DATETIME
	DECLARE @SQL              NVARCHAR(MAX)
	DECLARE @RecordsToDelete  INT
	DECLARE @RC               BIGINT
	DECLARE @RecordCount      BIGINT
	
	-- must pass table name parameter
	IF @Table IS NULL
	BEGIN
	    RAISERROR('Must specify @Table.', 16, 1)
	    RETURN 1
	END
	
	-- must pass value for prune parameter
	IF @HowMuch IS NULL
	BEGIN
	    RAISERROR('Must specify @HowMuch.', 16, 1)
	    RETURN 1
	END
	
	-- see if table exists
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    RAISERROR('Table ''%s'' not found in schema.', 16, 1, @Table)
	    RETURN 1
	END
	
	-- see if column named createdt exists
	IF dbo.fn_ColumnExists(@Table, 'CreateDt') = @False
	BEGIN
	    RAISERROR('Column ''%s'' not found in schema for table ''%s''.', 16, 1, 'CreateDt', @Table)
	    RETURN 1
	END
	
	-- make sure createdt is datetime
	IF dbo.fn_ColumnDatatype(@Table, 'CreateDt') <> 'DATETIME'
	BEGIN
	    RAISERROR('Column ''CreateDt'' is not datatype of ''DATETIME''.', 16, 1, @Table)
	    RETURN 1
	END
	
	-- check for usable value for @HowMuch
	IF (
	       SELECT CASE 
	                   WHEN @HowMuch = 'ALL' THEN @True
	                   WHEN @HowMuch = 'TRUNCATE' THEN @True
	                   WHEN ISDATE(@HowMuch) = 1 THEN @True
	                   WHEN @HowMuch LIKE '% Days' THEN @True
	                   WHEN @HowMuch LIKE '% Records' THEN @True
	                   ELSE @False
	              END
	   ) = @False
	BEGIN
	    RAISERROR('Invalid value for @HowMuch.', 16, 1)
	    RETURN 1
	END

	SET @Message = 'Start of Prune Table Process.'
	EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	
	SET @Message = '@Table = ''' + @Table + ''', ' + '@HowMuch = ''' + @HowMuch + ''', ' + '@ProcessName = ''' + @ProcessName + ''''
    EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance

/*
    SET @SQL = 'ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL'
    EXECUTE sp_executesql @SQL
    SELECT @ErrorCode = @@ERROR,
           @RowsAffected = @@ROWCOUNT
    IF @ErrorCode <> 0
    BEGIN
        SET @Message = 'Could not disable triggers for ''' + @Table + ''' table.'
        EXECUTE [dbo].utl_LogMsg @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
        RAISERROR(@Message, 16, 1)
        RETURN 1
    END
	ELSE
	BEGIN
        SET @Message = 'Disabled triggers for ''' + @Table + ''' table.'
        EXECUTE [dbo].utl_LogMsg @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	END
*/
	
	-- truncate table
	IF @HowMuch = 'TRUNCATE'
	BEGIN
	    SET @SQL = 'TRUNCATE TABLE ' + @Table
	    EXECUTE sp_executesql @SQL
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not truncate ''' + @Table + ''' table.'
	        EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	        RAISERROR(@Message, 16, 1)
	        RETURN 1
	    END
	    ELSE
	    BEGIN
	        SET @Message = 'Truncated ''' + @Table + ''' table.'
	        EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	    END
	END
	
	-- delete all records from table
	IF @HowMuch = 'ALL'
	BEGIN
	    SET @SQL = 'DELETE FROM TABLE ' + @Table
	    EXECUTE sp_executesql @SQL
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not delete all records from  ''' + @Table + ''' table.'
	        EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	        RAISERROR(@Message, 16, 1)
	        RETURN 1
	    END
	    ELSE
	    BEGIN
	        SET @Message = 'Deleted all records from  ''' + @Table + ''' table.'
	        EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	        IF dbo.fn_TableHasIdentityColumn(@Table) = @True
	        BEGIN
	            SET @SQL = 'DBCC CHECKIDENT (' + @Table + ', RESEED, 1)'
	            EXECUTE sp_executesql @SQL
	            SELECT @ErrorCode = @@ERROR,
	                   @RowsAffected = @@ROWCOUNT
	            IF @ErrorCode <> 0
	            BEGIN
	                SET @Message = 'Could not reset identity seed for table ' + @Table + '''.'
	                EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	                RAISERROR(@Message, 16, 1)
	                RETURN 1
	            END
	            ELSE
	            BEGIN
	                SET @Message = 'Reset identity seed for table ''' + @Table + '''.'
	                EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	            END
	        END
	    END
	END

	-- delete all records prior to date
	IF ISDATE(@HowMuch) = 1
	BEGIN
	    SET @PriorToDate = CAST(@HowMuch AS DATETIME)
	    
	    SET @SQL = 'DELETE FROM ' + @Table + ' WHERE [CreateDt] < ''' + CAST(@PriorToDate AS VARCHAR(20)) + ''''
	    EXECUTE sp_executesql @SQL
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not delete records older than ''' + CAST(@PriorToDate AS VARCHAR(20)) + ''' from ''' + @Table + ''' table.'
	        EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	        RAISERROR(@Message, 16, 1)
	        RETURN 1
	    END
	    ELSE
	    BEGIN
	        SET @Message = 'Deleted all records older than ''' + CAST(@PriorToDate AS VARCHAR(20)) + ''' from table ''' + @Table + '''.'
            EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	    END
	END
	
	-- handle number of days
	IF @HowMuch LIKE '% days'
	BEGIN
		IF ISNUMERIC(LEFT(@HowMuch, (CHARINDEX(@HowMuch, ' ')-1))) = @False
		BEGIN
			SET @Message = 'Could not understand @HowMuch parameter (' + @HowMuch + ').'
            EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
			RETURN 1
		END

		SET @Days = 0 - (LEFT(@HowMuch, (CHARINDEX(@HowMuch, ' ')-1)))
	    SET @SQL = 'DELETE FROM ' + @Table + ' WHERE [CreateDt] < DATEADD(d, ' + CAST(@Days AS VARCHAR(10)) + ' ,GETDATE())'
	    EXECUTE sp_executesql @SQL
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not delete records more than ''' + CAST(@Days AS VARCHAR(10)) + ''' days old from ''' + @Table + ''' table.'
            EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	        RAISERROR(@Message, 16, 1)
	        RETURN 1
	    END
	    ELSE
	    BEGIN
	        SET @Message = 'Deleted records more than ''' + CAST(@Days AS VARCHAR(10)) + ''' days old from ''' + @Table + ''' table.'
            EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	    END
	END
	
	-- handle number of records
	IF @HowMuch LIKE '% records'
	BEGIN
		IF ISNUMERIC(LEFT(@HowMuch, (CHARINDEX(@HowMuch, ' ')-1))) = @False
		BEGIN
			SET @Message = 'Could not understand @HowMuch parameter (' + @HowMuch + ').'
            EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
			RETURN 1
		END

		SET @Records = CAST(LEFT(@HowMuch, (CHARINDEX(@HowMuch, ' ')-1)) AS INT)

        SET @SQL = 'SELECT @RC = COUNT(*) FROM ' + @Table
        EXECUTE sp_executesql @SQL, N'@RC BIGINT OUT', @RecordCount OUT

		SET @Records = @RecordCount - @Records
		IF @Records > 0
		BEGIN
			SET @SQL = 'DELETE TOP ' + @Records + ' FROM ' + @Table + ' ORDER BY CreateDt ASC'
			EXECUTE sp_executesql @SQL
			SELECT @ErrorCode = @@ERROR,
				   @RowsAffected = @@ROWCOUNT
			IF @ErrorCode <> 0
			BEGIN
				SET @Message = 'Could not delete all but the latest ' + CAST(@Records AS VARCHAR(10)) + ' records from ''' + @Table + ''' table.'
				EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
				RAISERROR(@Message, 16, 1)
				RETURN 1
			END
			ELSE
			BEGIN
				SET @Message = 'Deleted all but the latest ' + CAST(@Records AS VARCHAR(10)) + ' records from ''' + @Table + ''' table.'
				EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
			END
		END
	END

	SET @Message = 'End of Prune Table process.'
	EXECUTE [dbo].[utl_LogMsg] @Message, @ErrorCode, @RowsAffected, @ProcessName, @StepName, @Instance
	
/*
    SET @SQL = 'ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL'
    EXECUTE sp_executesql @SQL
*/
	
	RETURN 0
END

